﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using WF_Business;

namespace ExtBusiness.OnLine
{
    /// <summary>
    /// 操作外网信息反馈  
    /// </summary>
    /// <!--addby zhongjian 20091103-->
    public class OnLineInfo
    {
        /// <summary>
        /// 获取留言咨询信息
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strID">留言ID</param>
        /// <param name="strMsgType">咨询类型(0:流程名称;1:部门KEY值)</param>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strDmpName">部门KEY值</param>
        /// <param name="strMsgCode">留言码</param>
        /// <param name="bAdmin">留言管理员标识</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091103-->
        public DataTable GetMessageInfo(string strUserID,string strID,string strMsgType,string strFlowName,string strDmpName,string strMsgCode,bool bAdmin)
        {
            string strSql = string.Format(@"select id,name,email,address,notes,sj,sfgk,zt,to_char(createdate,'yyyy-mm-dd hh24:mi') createdate,
                      getdepname(slbm) slbmname,flow,msgtype,msgcode,msshowtype,
                      wynewpackage.GetItemTypeName('问题类型', wtlx) TypeText,
                      (case when msgtype = 0 then '咨询事项'
                            when msgtype = 1 then '咨询部门'
                      end) msgtext,
                      (case when msshowtype=0 then '未显示' 
                            when msshowtype=1 then '已显示'
                      end) msshowtext
                      from xt_lyzx where 1=1 ");
            //当ID不为空时,不再添加其它条件
            if (!string.IsNullOrEmpty(strID))
            {
                strSql += string.Format(" and id='{0}'", strID);
            }
            else if (bAdmin)
            {
 
            }
            else
            {
                //当咨询类型不为空时,按咨询类型查询;否则不加类型条件 edit zhongjian 20091117
                if (!string.IsNullOrEmpty(strMsgType))
                {
                    strSql += string.Format(" and msgtype='{0}'", strMsgType);
                    if (strMsgType == "0")
                    {
                        strSql += string.Format(@" and flow in (select flowname from xt_workflow_define 
                                where id in (select trim(gid) from ST_USER_GROUP where userid ='{0}'))", strUserID);
                    }
                    if (strMsgType == "1")
                    {
                        strSql += string.Format(@" and slbm in (select trim(gid) from ST_USER_GROUP where userid ='{0}'))", strUserID);
                    }
                }
                else
                {
                    strSql += string.Format(@" and (slbm in
                      (select trim(gid) from ST_USER_GROUP where userid ='{0}')
                      or flow in (select flowname from xt_workflow_define 
                      where id in (select trim(gid) from ST_USER_GROUP where userid ='{0}')))", strUserID);
                }
                if (!string.IsNullOrEmpty(strFlowName))
                {
                    strSql += string.Format(" and flow='{0}'", strFlowName);
                }
                if (!string.IsNullOrEmpty(strDmpName))
                {
                    strSql += string.Format(" and slbm='{0}'", strDmpName);
                }
                if (!string.IsNullOrEmpty(strMsgCode))
                {
                    strSql += string.Format(" and msgcode='{0}'", strMsgCode);
                }
            }
            strSql += string.Format(" order by createdate desc");
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 添加留言咨询回复内容
        /// </summary>
        /// <param name="LyID">留言ID</param>
        /// <param name="UserID">用户ID</param>
        /// <param name="Notes">回复内容</param>
        /// <!--addby zhongjian 20091103-->
        public void InsertMessage(string LyID, string UserID,string Notes)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"insert into xt_lyzx_fk (ly_id,fk_notes,fk_userid) values ('{0}','{1}','{2}')", LyID, Notes, UserID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 按留言咨询信息
        /// </summary>
        /// <param name="strID">ID</param>
        /// <!--addby zhongjian 20100131-->
        public void DeleteLYZX(string strID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"delete xt_lyzx where id='{0}'", strID);
            DeleteLYZX_FK(strID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 按留言咨询ID删除所有回复记录
        /// </summary>
        /// <param name="strLYID">留言ID</param>
        /// <!--addby zhongjian 20100131-->
        public void DeleteLYZX_FK(string strLYID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"delete xt_lyzx_fk where ly_id='{0}'", strLYID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 按留言咨询回复ID删除回复记录
        /// </summary>
        /// <param name="strID">回复ID</param>
        /// <!--addby zhongjian 20091103-->
        public void DeleteMessage(string strID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"delete xt_lyzx_fk where id='{0}'", strID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 转发留言咨询
        /// </summary>
        /// <param name="LyID">留言ID</param>
        /// <param name="MsgType">咨询类型(true:咨询事项,false:咨询部门)</param>
        /// <param name="FlowName">流程名称</param>
        /// <param name="DepValue">部门KEY值</param>
        /// <!--addby zhongjian 20091118-->
        public void UpdateMessage(string LyID,bool MsgType,string FlowName,string DepValue)
        {
            string strSql = string.Empty;
            //将留言信息备份至历史表
            strSql = string.Format(@"insert into xt_lyzx_his (id,can_sync,sync_type,name,email,sex,address,dh,slbm,notes,sj,
                       wtlx,sfgk,zt,createdate,flow,msgtype,msgcode,msshowtype)
                       (SELECT id,can_sync,sync_type,name,email,sex,address,dh,slbm,notes,sj,
                       wtlx,sfgk,zt,createdate,flow,msgtype,msgcode,msshowtype from xt_lyzx where id='{0}')", LyID);
            SysParams.OAConnection().RunSql(strSql);
            
            //修改留言信息
            strSql = string.Format(@"update xt_lyzx");
            if (MsgType)
                strSql += string.Format(@" set flow='{0}'", FlowName);
            else
                strSql += string.Format(@" set slbm='{0}'", DepValue);
            strSql += string.Format(@" where id='{0}'", LyID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取一条留言咨询的回复信息
        /// </summary>
        /// <param name="strLYID">留言ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091103-->
        public DataTable GetMessageFKInfo(string strLYID)
        {
            string strSql = string.Format(@"select a.id,a.fk_notes,fk_date,b.user_name,fk_userid
                            from xt_lyzx_fk a ,st_user b where a.fk_userid=b.userid and  ly_id='{0}' order by fk_date", strLYID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 判断该用户是否为留言咨询管理员
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns>true:是管理员</returns>
        /// <!--addby zhongjian 20091202-->
        public bool GetMessageAdmin(string strUserID)
        {
            string strSql = string.Format(@"select id,userid,gid,can_sync,sync_type 
                            from ST_USER_GROUP where userid='{0}'", strUserID);//gid='MessageAdministrator'
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            string strAdmin = string.Empty;
            bool flag = false;
            if (dtReturn.Rows.Count > 0)
            {
                for (int i = 0; i < dtReturn.Rows.Count; ++i)
                {
                    strAdmin = dtReturn.Rows[i]["gid"].ToString();
                    if (strAdmin.Trim() == "MessageAdministrator")//当是管理员用户时
                    {
                        flag = true;
                        return flag;
                    }
                }
            }
            return flag;
        }

        /// <summary>
        /// 设置留言在外网的显示
        /// </summary>
        /// <param name="LyID">留言ID</param>
        /// <param name="ShowType">显示类型</param>
        /// <!--addby zhongjian 20091202-->
        public void SetMsgShow(string LyID,string ShowType)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"update xt_lyzx set msshowtype='{0}' where id='{1}'", ShowType, LyID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取投诉信息
        /// </summary>
        /// <param name="ID">投诉ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091103-->
        public DataTable GetComplaintInfo(string ID)
        {
            string strSql = string.Format(@"select id,iid,tsr,btsr,to_char(tssj,'yyyy-mm-dd hh24:mi') tssj,tsnr,
                            getdepname(tsbm) tsbm,wynewpackage.GetItemTypeName('投诉类型',tslx) tslx
                            from xt_tsinfo where 1=1 ");
            if (!string.IsNullOrEmpty(ID))
            {
                strSql += string.Format(" and id='{0}'", ID);
            }
            strSql += string.Format(" order by tssj desc");
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 获取一条投诉意见的回复信息
        /// </summary>
        /// <param name="strTSID">投诉ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091103-->
        public DataTable GetComplaintFKInfo(string strTSID)
        {
            string strSql = string.Format(@"select a.id,a.fk_notes,fk_date,b.user_name,fk_userid
                            from xt_tsinfo_fk a ,st_user b where a.fk_userid=b.userid and  ts_id='{0}' order by fk_date", strTSID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 添加投诉意见回复内容
        /// </summary>
        /// <param name="TSID">投诉ID</param>
        /// <param name="UserID">用户ID</param>
        /// <param name="Notes">回复内容</param>
        /// <!--addby zhongjian 20091103-->
        public void InsertComplaint(string TSID, string UserID, string Notes)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"insert into xt_tsinfo_fk (ts_id,fk_notes,fk_userid) values ('{0}','{1}','{2}')", TSID, Notes, UserID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 按留言咨询信息
        /// </summary>
        /// <param name="strID">ID</param>
        /// <!--addby zhongjian 20100131-->
        public void DeleteTsinfo(string strID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"delete xt_tsinfo where id='{0}'", strID);
            DeleteComplaint_FK(strID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 按留言咨询ID删除所有回复记录
        /// </summary>
        /// <param name="strTSID">留言ID</param>
        /// <!--addby zhongjian 20100131-->
        public void DeleteComplaint_FK(string strTSID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"delete xt_tsinfo_fk where ts_id='{0}'", strTSID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 按投诉意见回复ID删除投诉记录
        /// </summary>
        /// <param name="ID">回复ID</param>
        /// <!--addby zhongjian 20091103-->
        public void DeleteComplaint(string ID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"delete xt_tsinfo_fk where id='{0}'", ID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取服务质量反馈信息
        /// </summary>
        /// <param name="strID">留言ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091103-->
        public DataTable GetFeedbackInfo(string strID)
        {
            string strSql = string.Format(@"select id,name,email,dh,getdepname(slbm) slbm,fwzl,notes,flow,
            to_char(createdate,'yyyy-mm-dd hh24:mi') createdate from xt_fwfk where 1=1");
            if (!string.IsNullOrEmpty(strID))
            {
                strSql += string.Format(" and id='{0}'", strID);
            }
            strSql += string.Format(" order by createdate desc");
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 删除报务反馈信息
        /// </summary>
        /// <param name="ID"></param>
        /// <!--addby zhongjian 20100131-->
        public void DeleteFwfk(string ID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"delete xt_fwfk where id='{0}'", ID);
            SysParams.OAConnection().RunSql(strSql);
        }
    }
}
